﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.OleDb;
using System.Windows.Forms;
using System.IO;
using System.Data.SqlClient;

namespace CommonLib
{
    public class AccessDB
    {
        public OleDbConnection m_conn;
        public SqlConnection m_sqlConn;
        public string m_connString;
        private string dat_to_baseFile = "";
        private string pathStr;
        //private XMLConfig m_readConfig = new XMLConfig("MainConfig.xml");

        /// <summary>
        /// 构造函数
        /// </summary>
        /// <param name="dbPath"></param>
        public AccessDB()
        {
            dat_to_baseFile = "";
            //pathStr = "";
            //pathStr = Application.StartupPath;
            //pathStr = pathStr.Substring(0, pathStr.LastIndexOf("bin"));
            //pathStr += m_readConfig.GetAttributeValueFromSpcialAttribute("config", "name", "Access数据库路径", "value");
            pathStr = ConfigsAllocator.appStartPath + ConfigsAllocator.MainConfig.GetAttributeValueFromSpcialAttribute("config", "name", "Access数据库路径", "value");
            #region 链接数据库前对数据库进行解密
            if (pathStr.Contains("Dictionary.dic.dat") || pathStr.Contains("Function.fc.dat") || pathStr.Contains("UserData.safe.dat"))
            {
                dat_to_baseFile = pathStr.Substring(0, pathStr.ToString().Length - 4);
                m_connString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + dat_to_baseFile;
                m_conn = new OleDbConnection(m_connString);
                return;
            }
            #endregion
            m_connString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + pathStr;
            m_conn = new OleDbConnection(m_connString);
        }

        public AccessDB(string ConfigName)
        {
            dat_to_baseFile = "";
            //pathStr = "";
            //pathStr = Application.StartupPath;
            //pathStr = pathStr.Substring(0, pathStr.LastIndexOf("bin"));
            //pathStr += m_readConfig.GetAttributeValueFromSpcialAttribute("config", "name", ConfigName, "value");
            pathStr = ConfigsAllocator.appStartPath + ConfigsAllocator.MainConfig.GetAttributeValueFromSpcialAttribute("config", "name", ConfigName, "value");

            #region 链接数据库前对数据库进行解密
            if (pathStr.Contains("Dictionary.dic.dat") || pathStr.Contains("Function.fc.dat") || pathStr.Contains("UserData.safe.dat"))
            {
                dat_to_baseFile = pathStr.Substring(0, pathStr.ToString().Length - 4);
                m_connString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + dat_to_baseFile;
                m_conn = new OleDbConnection(m_connString);
                return;
            }
            #endregion
            m_connString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + pathStr;
            m_conn = new OleDbConnection(m_connString);
        }

        public AccessDB(string fullMdbPath, string funType)
        {
            //pathStr = "";
            //pathStr = Application.StartupPath;
            //pathStr = pathStr.Substring(0, pathStr.LastIndexOf("bin"));
            //pathStr += m_readConfig.GetAttributeValueFromSpcialAttribute("config", "name", ConfigName, "value");
            pathStr = fullMdbPath;
            m_connString = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" + pathStr;
            m_conn = new OleDbConnection(m_connString);
        }

        public AccessDB(string sqlServerAddress,string intialCatalog, string user, string code)
        {
            m_connString = "Data Source =" + sqlServerAddress + ";Initial Catalog=" + intialCatalog + ";User Id = " + user + ";Password=" + code + ";";
            m_sqlConn = new SqlConnection(m_connString);
        }
        #region OleDb数据库操作
        /// <summary>
        /// 打开数据库连接
        /// </summary>
        /// <returns></returns>
        public OleDbConnection connOpen()
        {
            if (!dat_to_baseFile.Equals(""))
            {
                #region 链接数据库前对数据库进行解密
                string password = "youyuan2004gis";//应用于此软件的密码
                DESFileClass.DecryptFile(pathStr, dat_to_baseFile, password);//解密文件 
                //删除解密前的文件 
                File.Delete(pathStr);
                #endregion
            }
            m_conn.Open();
            return m_conn;
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        /// <returns></returns>
        public void connClose()
        {
            m_conn.Close();
            if (!dat_to_baseFile.Equals(""))
            {
                #region 关闭数据库后对数据库进行加密
                string inFile = dat_to_baseFile;
                string outFile = inFile + ".dat";
                string password = "youyuan2004gis";
                DESFileClass.EncryptFile(inFile, outFile, password);//加密文件 
                //删除加密前的文件 
                File.Delete(inFile);
                #endregion
            }
        }

        //例如：返回符合sql要求的DataTable，并与dataGridView绑定
        //DataTable dt=new DataTable();
        //dt=accessDB.getDataTable(@"select * from 表名");
        //dataGridView.DataSource=dt;
        /// <summary>
        /// 根据sql语句，返回DataTable数据表
        /// </summary>
        /// <param name="sqlStr">sql语句</param>
        /// <returns></returns>
        public DataTable getDataTable(string sqlStr)
        {
            DataTable dt = new DataTable();

            connOpen();
            OleDbDataAdapter adapter = new OleDbDataAdapter();
            OleDbCommand command = new OleDbCommand(sqlStr, m_conn);
            adapter.SelectCommand = command;
            try
            {
                adapter.Fill(dt);
                //connClose();
                return dt;
            }
            catch
            {
                //connClose();
                return new DataTable();
            }
            finally
            {
                connClose();
            }
        }

        //例如：返回符合sql要求和给定DataTable名的DataSet，并与dataGridView绑定
        //DataSet ds=new DataSet();
        //ds=accessDB.getDataSet(@"select * from 表名","表名");
        //dataGridView.DataSource=ds.Table["表名"];
        /// <summary>
        /// 根据sql语句，返回DataSet数据集
        /// </summary>
        /// <param name="sqlStr">sql语句</param>
        /// <param name="subTableName">在返回的数据集中所添加的表的名称</param>
        /// <returns></returns>
        public DataSet getDataSet(string sqlStr, string subTableName)
        {
            DataSet ds = new DataSet();

            connOpen();
            OleDbDataAdapter adapter = new OleDbDataAdapter();
            OleDbCommand command = new OleDbCommand(sqlStr, m_conn);
            adapter.SelectCommand = command;
            try
            {
                adapter.Fill(ds, subTableName);
                return ds;
            }
            catch
            {
                return ds;
            }
            finally
            {
                connClose();
            }
        }

        /// <summary>
        /// 将指定名称的表，添加到指定数据集中
        /// （有覆盖已有表的危险，返回操作之前的数据集）
        /// </summary>
        /// <param name="sqlStr">sql语句</param>
        /// <param name="dataSet">被添加的数据集</param>
        /// <param name="subtableName">指定的表名</param>
        /// <returns></returns>
        public DataSet addTableToDataSet(string sqlStr, DataSet dataSet, string subtableName)
        {
            DataSet ds = new DataSet();
            ds = dataSet;

            connOpen();
            OleDbDataAdapter adapter = new OleDbDataAdapter();
            OleDbCommand command = new OleDbCommand(sqlStr, m_conn);
            adapter.SelectCommand = command;
            try
            {
                adapter.Fill(dataSet, subtableName);
                return ds;
            }
            catch
            {
                return ds;
            }
            finally
            {
                connClose();
            }
        }

        /// <summary>
        /// 根据sql语句返回OleDbDataAdapter
        /// </summary>
        /// <param name="sqlStr">sql语句</param>
        /// <returns></returns>
        public OleDbDataAdapter getOleDbDataAdapter(string sqlStr)
        {
            connOpen();
            OleDbDataAdapter adapter = new OleDbDataAdapter();
            try
            {
                OleDbCommand command = new OleDbCommand(sqlStr, m_conn);
                adapter.SelectCommand = command;
                return adapter;
            }
            catch
            {
                return adapter;
            }
            finally
            {
                connClose();
            }
        }

        //插入单行：insert into 表名(列名,…) values(列值,…);
        //插入多行：insert into 表名(列名,…) select子句;
        //删除单行：delete from 表名 where 列名=’列值’;
        //删除多行：delete from 表名 where 列名 IN (select…);
        //修改单行(元组)：update 表名 set 列名=表达式 where 列名=’列值’;
        //修改多行(元组)：update 表名 set 列名=表达式 where 列名 IN (select…);
        //修改多行(元组)：update 表名 set 列名=(select…) where 列名 IN (select…);
        /// <summary>
        /// 执行不需要返回数据的sql语句，如插入、删除、修改
        /// </summary>
        /// <param name="sqlStr">无数据返回的sql语句</param>
        /// <returns>成功返回“ok”，不成功错误信息</returns>
        public string executeSqlNonQuery(string sqlStr)
        {
            connOpen();
            OleDbCommand command = new OleDbCommand(sqlStr, m_conn);
            try
            {
                command.ExecuteNonQuery();
                return "ok";
            }
            catch (Exception ex)
            {
                return ex.Message;
            }
            finally
            {
                connClose();
            }
        }

        //插入单行：insert into 表名(列名,…) values(列值,…);
        //插入多行：insert into 表名(列名,…) select子句;
        //删除单行：delete from 表名 where 列名=’列值’;
        //删除多行：delete from 表名 where 列名 IN (select…);
        //修改单行(元组)：update 表名 set 列名=表达式 where 列名=’列值’;
        //修改多行(元组)：update 表名 set 列名=表达式 where 列名 IN (select…);
        //修改多行(元组)：update 表名 set 列名=(select…) where 列名 IN (select…);

        /// <summary>
        /// 执行不需要返回数据的sql语句，如插入、删除、修改
        /// </summary>
        /// <param name="sqlStr">无数据返回的sql语句</param>
        /// <returns>成功返回“ok”，不成功错误信息</returns>
        public bool executeSqlNonQuerybool(string sqlStr)
        {
            connOpen();
            OleDbCommand command = new OleDbCommand(sqlStr, m_conn);
            try
            {
                command.ExecuteNonQuery();
                return true;
            }
            catch
            {
                return false;
            }
            finally
            {
                connClose();
            }
        }

        public DataRow getDataRow(string sql)
        {
            DataTable dt = new DataTable();
            dt = this.getDataTable(sql);
            if (dt.Rows.Count == 1)
                return dt.Rows[0];
            else return null;
        }

        public List<string> getTableNameList()
        {
            List<string> tableNameList = new List<string>();
            DataTable dt = connOpen().GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "table" });
            try
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    string tableName = dt.Rows[i]["table_name"].ToString();
                    tableNameList.Add(tableName);
                }
                return tableNameList;
            }
            catch
            {
                return tableNameList;
            }
            finally
            {
                connClose();
            }
        }

        public DataTable getTableInfo(string tableName)
        {
            DataTable dt = new DataTable();
            DataColumn dc = new DataColumn("序号");
            dt.Columns.Add(dc);
            dc = new DataColumn("表名");
            dt.Columns.Add(dc);
            dc = new DataColumn("类型");
            dt.Columns.Add(dc);
            dc = new DataColumn("描述");
            dt.Columns.Add(dc);
            dc = new DataColumn("创建时间");
            dt.Columns.Add(dc);
            dc = new DataColumn("修改时间");
            dt.Columns.Add(dc);
            try
            {
                string[] restrictions = new string[4];
                //Restriction[0]表示表所在的Catalog，Restriction[1]表示表的所有者，Restriction[2]表示表的名字，Restriction[3]表示表的类型：
                restrictions[2] = tableName;
                DataTable table = connOpen().GetSchema("Tables", restrictions);
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    DataRow dr = dt.NewRow();
                    dr[0] = i.ToString();
                    dr[1] = table.Rows[i]["TABLE_NAME"].ToString();
                    dr[2] = table.Rows[i]["TABLE_TYPE"].ToString();
                    dr[3] = table.Rows[i]["DESCRIPTION"].ToString();
                    dr[4] = table.Rows[i]["DATE_CREATED"].ToString();
                    dr[5] = table.Rows[i]["DATE_MODIFIED"].ToString();
                    dt.Rows.Add(dr);
                }
                return dt;
            }
            catch
            {
                return dt;
            }
            finally
            {
                connClose();
            }
        }
        #endregion

        #region SqlServer数据库操作
        /// <summary>
        /// 打开数据库连接
        /// </summary>
        /// <returns></returns>
        public SqlConnection SqlConnOpen()
        {
            m_sqlConn.Open();
            return m_sqlConn;
        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        /// <returns></returns>
        public void SqlConnClose()
        {
            m_sqlConn.Close();
        }

        public DataTable getDataTableFromSqlServer(string sqlStr)
        {
            try
            {
                DataTable dt = new DataTable();
                SqlConnOpen();
                //创建命令
                SqlCommand scd = new SqlCommand();
                scd.Connection = m_sqlConn;
                scd.CommandText = sqlStr;
                SqlDataAdapter sqlAdapter = new SqlDataAdapter();
                sqlAdapter.SelectCommand = scd;
                DataSet ds = new DataSet();
                sqlAdapter.Fill(ds);
                return ds.Tables[0];
            }
            catch
            {
                return null;
            }
            finally
            {
                SqlConnClose() ;
            }
        }
        #endregion
    }
}
